package Sql;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map.Entry;
import fileConfig.operationXml;
import javaUnit.randomType;


public class mysql {
	static String filePath="resource/creaeDatabaseAndTable.xml";
	static HashMap<String, String> cnsql=operationXml.cnsql;
	static HashMap<String, HashMap<String, String>> tables=operationXml.tables;
	static operateSql ops;
	String sqlURL;
	String username;
	String password;
	String databaseName;
	
	public static void main(String[] args) throws Exception {
		mysql my = new mysql(filePath);
//		my.createDatabaseTable();
//		for (Entry<String, HashMap<String, String>> entry : tables.entrySet()) {
//			for (int i = 0; i < 1000; i++) {
//				my.insertTableData(entry.getKey());
//			}
//		}
		//my.randomSelectData("st",9);
		ArrayList<String> ls=my.otherCustomCmd("SELECT CODE FROM `captcha_code` ORDER BY id ASC LIMIT 1");
		if(ls.size()>1) System.out.println(ls.get(0));
	}
	
	public mysql(String filePath) {
		new operationXml("resource/creaeDatabaseAndTable.xml").readxml();
		this.databaseName=operationXml.cnsql.get("database");
		this.sqlURL=operationXml.cnsql.get("ip") + ":"+operationXml.cnsql.get("port");
		this.username=operationXml.cnsql.get("username");
		this.password=operationXml.cnsql.get("password");
		ops=new operateSql(sqlURL,username,password,databaseName);
	}

	/**
	 * 运行All SQL指令
	 * @param sqlCmd 指令
	 * @return
	 * @throws Exception
	 */
	public ArrayList<String> otherCustomCmd(String sqlCmd) throws Exception{
		return ops.runCmdSql(sqlURL+"/"+databaseName,sqlCmd);
	}
	
	/**
	 * 创键database和table
	 * 
	 * @throws Exception
	 */
	public void createDatabaseTable(){;
		ops.createDatabase(databaseName);
		for (Entry<String, HashMap<String, String>> entry : tables.entrySet()) {
			ops.createTable(entry.getKey(),entry.getValue());
		}
	}

	/**
	 * 新增数据
	 * 
	 * @param tableName
	 * @param number
	 * @throws Exception
	 */
	public ArrayList<String> insertTableData(String tableName) {
		StringBuilder conCmd = new StringBuilder();
		conCmd.append("insert into " + tableName + " set ");
		conCmd.append(returnTableKV(tableName));
		conCmd.append(";");
		return ops.runCmdSql(sqlURL+"/"+databaseName, conCmd.toString());
	}

	/**
	 * 随机返回一组数据的ID
	 * 
	 * @param tableName
	 * @param number
	 * @return
	 * @throws Exception
	 */
	public ArrayList<String> randomSelectData(String tableName,
			int number){
		String cmdsql = "select id from " + tableName
				+ " order by RAND() limit " + number + ";";
		return ops.runCmdSql(sqlURL+"/"+databaseName, cmdsql);
		// select id from st order by RAND() limit 1
	}

	/**
	 * 单表查询
	 * @param tableName 表名
	 * @param condition 条件
	 * @return
	 * @throws Exception
	 */
	public ArrayList<String> SelectTableData(String tableName,String condition){
		String  cmdsql="select * from "+tableName+" "+condition+";";
		return ops.runCmdSql(sqlURL+"/"+databaseName, cmdsql);
	}
	
	/**
	 * 单表查询
	 * @param tableName 表名
	 * @param condition 条件
	 * @param tableColumn 返回的结果
	 * @return
	 * @throws Exception
	 */
	public ArrayList<String> SelectTableData(String tableName,String condition,String tableColumn){
		String  cmdsql="select "+tableColumn+" from "+tableName+" "+condition+";";
		return ops.runCmdSql(sqlURL+"/"+databaseName, cmdsql);
	}
	
	/**
	 * 联合查询内连接
	 * @param tableName1 表名字
	 * @param tableName2 表名字
	 * @param condition 查询条件
	 * @return
	 * @throws Exception
	 */
	public ArrayList<String> SelectTablesData(String tableName1,String tableName2,String condition){
		String  cmdsql="select * from "+tableName1+" inner join "+tableName2+" on "+condition+";" ;
		return ops.runCmdSql(sqlURL+"/"+databaseName, cmdsql);
	}
	
	/**
	 * 联合查询内连接
	 * @param tableName1 表名字
	 * @param tableName2 表名字
	 * @param condition 查询条件
	 * @param returnKV 显示的结果
	 * @return
	 * @throws Exception
	 */
	public ArrayList<String> SelectTablesData(String tableName1,String tableName2,String condition,String returnKV){
		String  cmdsql="select "+returnKV+" from "+tableName1+" inner join "+tableName2+" on "+condition+";" ;
		return ops.runCmdSql(sqlURL+"/"+databaseName, cmdsql);
	}
	
	
	/**
	 * 随机删除数据
	 * 
	 * @param tableName 表名
	 * @param condition 删除数据条件,为空时清除所有数据
	 * @param defaults  true 表清除所有数据并恢复默认
	 * @throws Exception
	 */
	public ArrayList<String> delTableData(String tableName,
			String condition,boolean defaults){
		String cmdsql=null;
		cmdsql = "delete from " +tableName+" "+condition+";";
		if(defaults) cmdsql="TRUNCATE table " + tableName + ";";
		return ops.runCmdSql(sqlURL+"/"+databaseName, cmdsql);
	}

	/**
	 * 更新数据
	 * 
	 * @param tableName 表明
	 * @param condition 更新条件
	 * @throws Exception
	 */
	public  ArrayList<String> updateTableData(String tableName,
			String condition) throws Exception {
		String cmdsql = "update " + tableName + " set " + condition + ";";
		return ops.runCmdSql(sqlURL+"/"+databaseName, cmdsql);
	}

	/**
	 * 返回数据库的连接对象
	 * 
	 * @return 返回连接对象
	 * @throws Exception
	 */
	public Connection connectSql(){
		Connection conn = ops.connectSql(sqlURL+"/"+databaseName,cnsql.get("username"),cnsql.get("password"));		
		return conn;
	}

	/**
	 * 返回table一组数据
	 * 
	 * @param tableName 表名
	 * @return
	 */
	public static String returnTableKV(String tableName) {
		StringBuilder conCmd = new StringBuilder();
		HashMap<String, String> tablekv = tables.get(tableName);
		int t = 1;
		for (Entry<String, String> entry : tablekv.entrySet()) {
			conCmd.append(entry.getKey() + "="
					+ randomType.reValue(entry.getValue()));
			if (tablekv.size() != t)
				conCmd.append(",");
			t++;
		}
		return conCmd.toString();
	}
}
